package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Department;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class ReportAMP extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private int HR_Block_ID = 0;
	/** */
	private int HR_Department_ID = 0;
	/** */
	X_HR_Department block = null;
	/** */
	 X_HR_Department department = null;
	/** */
	private String path = "";
	/** */
	Timestamp startDate;	
	Timestamp endDate;
	SimpleDateFormat dateFormat = new SimpleDateFormat("ddMMyyyy");
	
	
	private List<Object> param = new ArrayList<Object>();

	@Override
	protected void prepare() {
		
		log.info(" Report AMP ");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo("HR_Block_ID") == 0 && para[i].getParameter() != null)
				HR_Block_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("HR_Department_ID") == 0 && para[i].getParameter() != null)
				HR_Department_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else if (name.compareTo("StartDate") == 0){
				startDate = (Timestamp) para[i].getParameter();
				endDate = (Timestamp) para[i].getParameter_To();
			}
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");

		department = new X_HR_Department(getCtx(), HR_Department_ID, null);
		block = new X_HR_Department(getCtx(), HR_Block_ID, null);

	}

	@Override
	protected String doIt() throws Exception {

		// Types of cells for
		Label label;
		Number number;
		Formula formula;
		// There are checks for a specified pattern
		String templateName = "Отчет_по_численности_персонала_за_период_с_" + dateFormat.format(startDate) + "_по_" + dateFormat.format(endDate);
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate") + " " + templateName);
		
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path);		
		fullPath.append(templateName).append(fileExtension);
		String sampleTemp = Util.localFilePath + "AdempiereTemp";
				
		// 
		File templateCopy = new File(fullPath.toString());
		File sampleCopy = new File(sampleTemp);
		File temporaryFile = entry.getFile(Util.localFilePath + templateName);
		File temporarySample = entry.getFile(Util.localFilePath + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		// for sample
		Workbook sample = null;
		WritableWorkbook samCopy = null;
		WritableSheet sheet_1 = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
			// for sample
			 sample = Workbook.getWorkbook(temporarySample);
			 samCopy = Workbook.createWorkbook(sampleCopy, sample);
			 sheet_1 = samCopy.getSheet(0);
			 samCopy.write();
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
		WritableCellFormat borderStyle = new WritableCellFormat();
		borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		borderStyle.setAlignment(Alignment.CENTRE);
		borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		borderStyle.setWrap(true);
		// Title style cell
		WritableFont fontTitle = new WritableFont (WritableFont.TIMES, 14, WritableFont.NO_BOLD);
		WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
		titleStyle.setAlignment(Alignment.CENTRE);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		// Department style cell
		WritableCellFormat cellDep = new WritableCellFormat();
		cellDep.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		cellDep.setAlignment(Alignment.LEFT);
		cellDep.setVerticalAlignment(VerticalAlignment.CENTRE);
		cellDep.setWrap(true);
		// Percent format
		WritableFont font = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
		WritableCellFormat percentf = new WritableCellFormat(font, NumberFormats.PERCENT_INTEGER);
		percentf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		percentf.setAlignment(Alignment.CENTRE);
		percentf.setVerticalAlignment(VerticalAlignment.CENTRE);
		// Total
		WritableCellFormat cellTotal = new WritableCellFormat(font);
		cellTotal.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		cellTotal.setAlignment(Alignment.CENTRE);
		cellTotal.setVerticalAlignment(VerticalAlignment.CENTRE);
		
	    // header report
	    WritableCell readCell = sheet.getWritableCell(1, 1);
		CellFormat readFormat = readCell.getCellFormat();
		label = new Label(1, 1, sheet.getCell(1, 1).getContents() + Util.getOrgInfo().getDescription(), readFormat);
		sheet.addCell(label);
		
		Object[] param = new Object[]{Util.getMonthName(new Timestamp(startDate.getTime()), Env.getAD_Language(getCtx()), false), 
									  Integer.toString(new org.joda.time.DateTime(startDate.getTime()).getYear()),
									  Util.getMonthName(new Timestamp(endDate.getTime()), Env.getAD_Language(getCtx()), false),
									  Integer.toString(new org.joda.time.DateTime(endDate.getTime()).getYear())};
		if(new org.joda.time.DateTime(startDate.getTime()).getMonthOfYear() != new org.joda.time.DateTime(endDate.getTime()).getMonthOfYear())
			label = new Label(1, 2, sheet.getCell(4, 3).getContents()+MessageFormat.format(" {0} {1} - {2} {3} года", param), titleStyle);
		else
			label = new Label(1, 2, MessageFormat.format(" за {0} месяц {1} года", param), titleStyle);
		sheet.addCell(label);
		
		ListAMP[] lines = getListAMP();
		X_HR_Department department = null;
		int col = cellStart.getC(); // offset by the number of first columns
		int row = cellStart.getR();
		int i = 0;
		int rowC = row;
		for(int j = row; j < (row+lines.length); j++){
			department = new X_HR_Department(getCtx(), lines[i].getHR_Department_ID(), get_TrxName());
			// Department
			label = new Label(col, j, department.getName(), cellDep); 
			sheet.addCell(label);
			// Count Fact Employee
			number = new Number(col+1,j, lines[i].getCntEmp(), borderStyle); 
			sheet.addCell(number);
			// Count Employee Staff
			number = new Number(col+2,j, lines[i].getCntStaff(), borderStyle); 
			sheet.addCell(number);
			// Count Management
			number = new Number(col+3,j, lines[i].getCntManagement(), borderStyle); 
			sheet.addCell(number);
			// Count Management
			if(lines[i].getCntStaff() != 0)
				number = new Number(col+4,j, (lines[i].getCntManagement()/lines[i].getCntStaff()), percentf);
			else
				number = new Number(col+4,j, 0, percentf);
			sheet.addCell(number);
						
			i++;
			rowC++;
		}
		
		label = new Label(col, rowC, "ИТОГО:", cellTotal);
		sheet.addCell(label);
		formula = new Formula(col+1, rowC, "SUM(C7:C"+rowC+")", borderStyle);
		sheet.addCell(formula);
		formula = new Formula(col+2, rowC, "SUM(D7:D"+rowC+")", borderStyle);
		sheet.addCell(formula);
		formula = new Formula(col+3, rowC, "SUM(E7:E"+rowC+")", borderStyle);
		sheet.addCell(formula);
		formula = new Formula(col+4, rowC, "E"+(rowC+1)+"/D"+(rowC+1), percentf);
		sheet.addCell(formula);

		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();
		
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));

		return Msg.translate(m_ctx, "Success");
	}
	
	private ListAMP[] getListAMP(){
		
		ListAMP values = null;
		List<ListAMP> list = new ArrayList<ListAMP>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;		

		//param.add(new BigDecimal(getAD_Client_ID()));

		try
		{
			pstmt = DB.prepareStatement(getSql(),get_TrxName());
			DB.setParameters(pstmt, param);
			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				values = new ListAMP();
				values.setHR_Department_ID(rs.getInt(1));
				values.setCntStaff(rs.getDouble(2));
				values.setCntEmp(rs.getInt(3));
				values.setCntManagement(rs.getInt(4));
				list.add(values);
				values=null;
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new ListAMP[list.size()]);
	}

	private String getSql(){
		
		StringBuilder sql = new StringBuilder("with ");
		// with t1 ->>
		sql.append("t1 as( \n");
		sql.append("select t.hr_department_id, sum(r.hrm_vacancy+r.hrm_workplace) as staffcnt from hr_department t \n");
		sql.append("join hr_job r on r.hr_department_id=t.hr_department_id \n");
		sql.append("where t.hrm_units='D' AND t.ISACTIVE='Y' AND r.ISACTIVE='Y' \n");
		sql.append("group by t.hr_department_id order by t.description::numeric \n");
		if (HR_Department_ID != 0){
			sql.append(" AND T.HR_DEPARTMENT_ID="+HR_Department_ID+" \n");
		}
		else if(HR_Block_ID !=0){
			sql.append(" AND T.HR_DEPARTMENT_ID IN (SELECT NODE_ID FROM AD_TREENODEDEP WHERE PARENT_ID= "+HR_Block_ID+") \n");			
		}
		sql.append("), \n");
		// t1 <<--
		// t3 -->>
		sql.append("t3 as( \n select b.*, ( \n");
		sql.append("select count(*)::numeric(3,0) from hr_employee t \n");
		sql.append("join hr_contract r on r.c_bpartner_id=t.c_bpartner_id and r.hrm_contracttype='1' \n");
		sql.append("where r.hrm_trialperiod_ID is null and r.isactive='Y' \n");
		sql.append("and t.startdate = (SELECT MAX(H.startdate) FROM HR_EMPLOYEE H WHERE H.C_BPARTNER_ID=t.C_BPARTNER_ID and H.startdate <= "+DB.TO_DATE(endDate, true)+") \n");
		sql.append("and t.hr_department_id = b.hr_department_id \n");
		sql.append("and r.validfrom = (SELECT MAX(c.validFrom) FROM HR_CONTRACT C WHERE C.C_BPARTNER_ID=t.C_BPARTNER_ID and c.validFrom <= "+DB.TO_DATE(endDate, true)+") \n");		
		sql.append(") as cntEmp from t1 b \n ),");
		// t3 <<-
		// t4 -->>
		sql.append("t4 as ( \n select c.*, ( \n");
		sql.append("select sum(t.hrm_vacancy+t.hrm_workplace) from hr_job t \n");
		sql.append("where t.hr_department_id=c.hr_department_id and t.ismanagement='Y' and t.ISACTIVE='Y' \n");	
		sql.append(") as cntmanagement from t3 c \n ) \n");
		// t4 <<--
		sql.append("select * from t4");
		
		return sql.toString();		
	}
	
	class ListAMP{
		
		private int HR_Department_ID;
		private double cntStaff;
		private int cntEmp;
		private int cntManagement;
		
		public int getHR_Department_ID() {
			return HR_Department_ID;
		}
		public void setHR_Department_ID(int hR_Department_ID) {
			HR_Department_ID = hR_Department_ID;
		}
		public double getCntStaff() {
			return cntStaff;
		}
		public void setCntStaff(double cntStaff) {
			this.cntStaff = cntStaff;
		}
		public int getCntEmp() {
			return cntEmp;
		}
		public void setCntEmp(int cntEmp) {
			this.cntEmp = cntEmp;
		}
		public int getCntManagement() {
			return cntManagement;
		}
		public void setCntManagement(int cntManagement) {
			this.cntManagement = cntManagement;
		}
		
	}

}
